package com.neuedu.javase.jdbc;

import org.junit.Test;

import java.util.Date;
import java.sql.*;

/**
 * @auth 金山老师
 * title:   书籍代码
 * creatime: 2022-10-10 14:23
 * 描述 :
 */
public class JDBCTest {


    @Test   //注解
    public void select(){

        String url = "jdbc:mysql://localhost:3306/jdbc_hrb20?useSSL=false&useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "root"; // ?????确认下
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url,username,password);
            stmt = con.createStatement();

            String sql = " select * from user ";
            rs = stmt.executeQuery(sql);

            //解析结果集
            ResultSetMetaData meta = rs.getMetaData();//元数据
            int columnCount = meta.getColumnCount();//查询结果中一共有多少个字段
            System.out.println("一共多少列 columnCount = " + columnCount);
            for (int i = columnCount; i > 0; i--) {
                String columnLabel = meta.getColumnLabel(i);
                int type = meta.getColumnType(i);
                String typeName = meta.getColumnTypeName(i);
//                meta.get   decimail(10,2) 整数为8 小数位2
                System.out.println("columnLabel = " + columnLabel+"\ttypeName:"+typeName+"\ttype:"+type);
            }

            //解析数据
            /**
             *  `id`,
             *   `username`,
             *   `displayname`,
             *   `password`,
             *   `birthday`,
             *   `gender`,
             *   `telephone`,
             *   `email`,
             *   `last_logintime`,
             *   `create_time`,
             *   `valid` 
             */
            //jdbc的序号 从1 开始
           while(rs.next()) {  //1 判断是否有下一行数据 ，2 如果有下一行，指向数据行
               //rs 执行数据行
//               int id = rs.getInt(1);// id列
               int id = rs.getInt("id");// id列
               String uname = rs.getString("username");// username列
               String displayname = rs.getString("displayname");
               Date birthday = rs.getDate("birthday");    //java.sql.Date 只包含年、月、日
               String gender = rs.getString("gender");
               String telephone = rs.getString("telephone");
               String email = rs.getString("email");
               Date last_logintime = rs.getTimestamp("last_logintime");  // java.sql.Timestamp(Java.util.Date)   年 月 日  时分秒
               Date create_time = rs.getTimestamp("create_time"); // java.sql.Timestamp(Java.util.Date)   年 月 日  时分秒
               String valid = rs.getString("valid");
               System.out.printf("id:%d\tusername:%s\tdispalyName:%s\tbirthday:%s\t gender:%s \ttelephone:%s \temail:%s\tlast_logintime:%s\tcreate_time:%s\tvalid:%s\r\n",
                       id,
                       uname,
                       displayname,
                       birthday,
                       gender,
                       telephone,
                       email,
                       last_logintime,
                       create_time,
                       valid
                       );
           }



        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(con != null){
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }


        }

    }

    @Test   //注解
    public void delete(){
        /*
            1 加载驱动(内存中) 在内存中注册Driver
            2 创建连接 Connection
                url、用户名、密码
            3 创建 Statement对象
            4 执行SQL语句
                更新  insert  update  delete
                查询  select  --->  ResultSet
            5  解析结果接 ResultSet  -> long  Map  User     List<Map>  List<User>
            6 关闭资源：
                ResultSet
                Statement
                Connection
         */

        String url = "jdbc:mysql://localhost:3306/jdbc_hrb20?useSSL=false&useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "root"; // ?????确认下
        Connection con = null;
        Statement stmt = null;
        try {
            //1 加载驱动(内存中) 在内存中注册DrDriverManager iver
            Class.forName("com.mysql.jdbc.Driver");
            //2 创建连接
            con = DriverManager.getConnection(url,username,password);

            //3 创建 Statement对象
            stmt = con.createStatement();

            StringBuilder sql = new StringBuilder();
            sql.append("    delete from user   WHERE id = 3                              ");
            int count = stmt.executeUpdate(sql.toString());

            System.out.println("删除成功的 count = " + count);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(con != null){
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }


    @Test   //注解
    public void update(){
        /*
            1 加载驱动(内存中) 在内存中注册Driver
            2 创建连接 Connection
                url、用户名、密码
            3 创建 Statement对象
            4 执行SQL语句
                更新  insert  update  delete
                查询  select  --->  ResultSet
            5  解析结果接 ResultSet  -> long  Map  User     List<Map>  List<User>
            6 关闭资源：
                ResultSet
                Statement
                Connection
         */

        String url = "jdbc:mysql://localhost:3306/jdbc_hrb20?useSSL=false&useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "root"; // ?????确认下
        Connection con = null;
        Statement stmt = null;
        try {
            //1 加载驱动(内存中) 在内存中注册DrDriverManager iver
            Class.forName("com.mysql.jdbc.Driver");
            //2 创建连接
            con = DriverManager.getConnection(url,username,password);

            //3 创建 Statement对象
            stmt = con.createStatement();

            StringBuilder sql = new StringBuilder();
            sql.append("    UPDATE 										");
            sql.append("      `user`                                    ");
            sql.append("    SET                                         ");
            sql.append("      `username` = 'superroot',                      ");
            sql.append("      `displayname` = '超级管理员',             ");
            sql.append("      `password` = '123456',                    ");
            sql.append("      `birthday` = '2000-01-15',                ");
            sql.append("      `gender` = '0',                           ");
            sql.append("      `telephone` = '15888888888',              ");
            sql.append("      `email` = 'aaa@mail.com',                 ");
            sql.append("      `last_logintime` = '2022-10-10 15:44:27'  ");
            sql.append("                                                ");
            sql.append("    WHERE `id` = 3                              ");
            int count = stmt.executeUpdate(sql.toString());

            System.out.println("count = " + count);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(con != null){
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }


        }
    }
    
    
    public void insert(){
        /*
            1 加载驱动(内存中) 在内存中注册Driver
            2 创建连接 Connection
                url、用户名、密码
            3 创建 Statement对象
            4 执行SQL语句
                更新  insert  update  delete
                查询  select  --->  ResultSet
            5  解析结果接 ResultSet  -> long  Map  User     List<Map>  List<User>
            6 关闭资源：
                ResultSet
                Statement
                Connection
         */

        String url = "jdbc:mysql://localhost:3306/jdbc_hrb20?useSSL=false&useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "root"; // ?????确认下
        Connection con = null;
        Statement stmt = null;
        try {
            //1 加载驱动(内存中) 在内存中注册DrDriverManager iver
            Class.forName("com.mysql.jdbc.Driver");
            //2 创建连接
            con = DriverManager.getConnection(url,username,password);

            //3 创建 Statement对象
            stmt = con.createStatement();

            String sql = " insert  into `user`(`username`,`displayname`,`password`,`birthday`,`gender`,`telephone`,`email`,`last_logintime`,`create_time`,`valid`) values ('admin','管理员','123456','2021-11-25','1','13888888888',NULL,NULL,'2021-11-25 11:38:30','1') ";
            int count = stmt.executeUpdate(sql);

            System.out.println("count = " + count);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {


            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(con != null){
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }


        }
    }



    public static void main(String[] args) {
        new JDBCTest().insert();
    }
}
